/**
 * 
 */
package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.Route;

/**
 * A class used for managing route
 */
public class RouteDAO {

	/** a database of this route */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public RouteDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all route in database
     * @return all route
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM route";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    public List<Map<String, Object>> findRouteBySiteId(int pnSiteId) {
       // String vaSql = "SELECT zone_id,route_id,route_description FROM route WHERE site_id=?";
    	String vaSql ="SELECT ZN.ZONE_NAME ,RT.ROUTE_ID,RT.ROUTE_DESCRIPTION,RD.READER_NAME,RT.ZONE_ID"
    			+ " FROM ROUTE RT "
    			+ " JOIN READER RD  ON RT.READER_ID=RD.READER_ID AND RT.READER_ID <> 0  AND RT.SITE_ID = '"+pnSiteId+ "' "
    			+ " JOIN ZONE ZN ON RT.ZONE_ID = ZN.ZONE_ID "
    			+ " ORDER BY RT.READER_ID ASC";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    /**
     * a method for searching route by zone in database
     * @return routes where are in the zone
     */
    public List<Map<String, Object>> findRouteByZone(int pnSiteId, int pnZoneId) {
        String vaSql = "SELECT route_id, route_description, reader_id FROM route WHERE site_id = ? and zone_id = ? ORDER BY route_id ASC";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public String findRouteNameBySiteAndRouteId(int pnSiteId, int pnZoneId, int pnRouteId) {
        String vaSql = "SELECT route_description FROM route WHERE site_id = ? and zone_id = ? and route_id = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId, pnRouteId);
        if (result != null) {
            return (String) result.get("ROUTE_DESCRIPTION");
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findReaderIdByRoute(int pnSiteId, int pnZoneId, int pnRouteId) {
        String vaSql = "SELECT reader_id FROM route WHERE site_id = ? AND zone_id = ? AND route_id = ?";
        List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId, pnRouteId);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    //get zone_id and route_id ว่าซ้ำกันไหม ตอน insert ข้อมูล
    public List<Map<String, Object>> isCheckZoneAndRoute(int pnSiteId, int pnZoneId, int pnRouteId) {
        String vaSql = "SELECT ZONE_ID,ROUTE_ID "
        		+ "	FROM ROUTE  "
        		+ "	WHERE ZONE_ID  = '"+pnZoneId+ "'  "
        		+ "	AND  ROUTE_ID = '"+pnRouteId+ "'  "
        		+ "	AND  SITE_ID = '"+pnSiteId+ "'  ";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    //get route_id AND zone_id AND route_description 
    public String findRouteBySiteAndRouteId(int pnSiteId, int pnZoneId,int pnRouteId,String paField) {
        String vaSql = "SELECT "+ paField +" FROM route WHERE site_id = ? and zone_id = ?and route_id= ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId,pnRouteId);
        if (result != null) {
            return result.get(paField).toString();
        } else {
            return null;
        }
    }

    //insert route
    public void addRoute(Route poRoute) {
   	 String vaSql = "INSERT INTO ROUTE (SITE_ID,ZONE_ID,ROUTE_ID,ROUTE_DESCRIPTION,READER_ID,INSERT_USER_ID,INSERT_DATE,INSERT_TIME,"
   	 								+ "UPDATE_USER_ID,UPDATE_DATE,UPDATE_TIME)"
   			 						+"VALUES(?,?,?,?,?,?,?,?,?,?,?)";
   	//System.out.println(vaSql);
   			try {
   					db.insert(vaSql, poRoute.getVnSiteId(),
   							poRoute.getVnZoneId(),
   							poRoute.getVnRouteId(),
   							poRoute.getVaRouteDescription(),
   							poRoute.getVnReaderId(),
   							poRoute.getVnInsertUserId(),
   							poRoute.getVaInsertDate(), 
   							poRoute.getVaInsertTime(),
   							poRoute.getVnUpdateUserId(),
   							poRoute.getVaUpdateDate(),
   							poRoute.getVaUpdateTime());
   				
   			}catch (Exception e) {
   	        	throw new RuntimeException(e);
   	        }
   	}
    
    //update route  
    public void updateRoute(int pnSiteId,int pnZoneId,int pnRouteId,Route poRoute) {
	 	 
    	String vaSql  = " UPDATE ROUTE SET " +
    			  	  " ZONE_ID = ? , " +
    				  " ROUTE_ID = ? , " +
    				  " ROUTE_DESCRIPTION  = ? , " +
    				  " READER_ID = ? , " +
    				  " UPDATE_USER_ID = ? ,"+
    				  " UPDATE_DATE = ? ," +
    				  " UPDATE_TIME = ? " +
    				  " WHERE " + 
    				  " ZONE_ID = ? " +
    				  " AND ROUTE_ID = ? " +
    				  " AND SITE_ID = ? ";
    	
    	 try {
				db.update(vaSql,
						poRoute.getVnZoneId(),
						poRoute.getVnRouteId(),
						poRoute.getVaRouteDescription(),
						poRoute.getVnReaderId(),
						poRoute.getVnUpdateUserId(),
						poRoute.getVaUpdateDate(),
						poRoute.getVaUpdateTime(),
						pnZoneId,
						pnRouteId,
						pnSiteId);
							
		}catch (Exception e) {
			throw new RuntimeException(e);
		}
    }
    
    //delete route
    public void  deleteRouteBySiteId(int pnSiteId,int pnZoneID,int pnRouteId) {
        String vaSql = "DELETE FROM ROUTE "
        		+ "  WHERE SITE_ID  = '"+pnSiteId+ "'  "
        		+ "  AND ZONE_ID  = '"+pnZoneID+ "'  "
        		+ "  AND ROUTE_ID  = '"+pnRouteId+ "'  ";
    	/*String vaSql  = " UPDATE ROUTE SET " +
				  " READER_ID = 0  " +
				  " WHERE " + 
				  " ZONE_ID = '"+pnZoneID+ "'  "+
				  " AND ROUTE_ID  = '"+pnRouteId+ "'  "+
				  " AND SITE_ID = '"+pnSiteId+ "'  " ;*/
        try {
            db.update(vaSql);
            db.commit();
        }catch (Exception e) {
			throw new RuntimeException(e);
		}
    }
    
}
